package com.ihr360.oneclick.service.impl;

import com.alibaba.fastjson.JSONArray;
import com.google.common.collect.Lists;
import com.ihr360.excel.ExcelUtil;
import com.ihr360.excel.entity.ExportParams;
import com.ihr360.excel.util.ExcelDateFormatUtil;
import com.ihr360.oneclick.constant.SpreadsheetRowColumnMeta;
import com.ihr360.oneclick.dao.OneClickTermDao;
import com.ihr360.oneclick.dao.SpreadsheetColumnDao;
import com.ihr360.oneclick.dao.SpreadsheetDao;
import com.ihr360.oneclick.dao.SpreadsheetRowDao;
import com.ihr360.oneclick.dao.SpreadsheetRowHeaderDao;
import com.ihr360.oneclick.dao.SpreadsheetTempRowDao;
import com.ihr360.oneclick.dao.StaffRecordDao;
import com.ihr360.oneclick.dao.UserStaffRecordDao;
import com.ihr360.oneclick.dto.OneClickTermDto;
import com.ihr360.oneclick.dto.StaffRecordDto;
import com.ihr360.oneclick.dto.UserStaffRecordDto;
import com.ihr360.oneclick.dto.spreadsheet.SpreadSheetRowHeaderDto;
import com.ihr360.oneclick.dto.spreadsheet.SpreadsheetColumnDto;
import com.ihr360.oneclick.dto.spreadsheet.SpreadsheetDto;
import com.ihr360.oneclick.dto.spreadsheet.SpreadsheetRowDto;
import com.ihr360.oneclick.dto.spreadsheet.SpreadsheetTempRowDto;
import com.ihr360.oneclick.dto.spreadsheet.UserSpreadsheetDto;
import com.ihr360.oneclick.entity.IdCardType;
import com.ihr360.oneclick.entity.OneClickTerm;
import com.ihr360.oneclick.entity.RowHeaderColumn;
import com.ihr360.oneclick.entity.SpreadSheetRowHeader;
import com.ihr360.oneclick.entity.Spreadsheet;
import com.ihr360.oneclick.entity.SpreadsheetRow;
import com.ihr360.oneclick.entity.StaffRecord;
import com.ihr360.oneclick.exception.OneClickException;
import com.ihr360.oneclick.modal.ExcelImportContext;
import com.ihr360.oneclick.modal.StaffSpreadsheetRow;
import com.ihr360.oneclick.request.SearchRequest;
import com.ihr360.oneclick.request.SpreadsheetImportRequest;
import com.ihr360.oneclick.service.ExcelHandlerService;
import com.ihr360.oneclick.service.RowHeaderColumnService;
import com.ihr360.oneclick.service.SpreadsheetRowService;
import com.ihr360.oneclick.service.SpreadsheetService;
import com.ihr360.oneclick.service.SpreadsheetTempRowService;
import com.ihr360.oneclick.service.StaffRecordService;
import com.ihr360.oneclick.util.BeanConvertUtils;
import com.ihr360.oneclick.util.ExcelImportValidator;
import com.ihr360.oneclick.vo.ExcelImportResult;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * excel 处理类
 *
 * @author tom
 * @date 18-1-4
 */
@Service
public class ExcelHandlerServiceImpl implements ExcelHandlerService {

    @Autowired
    private SpreadsheetDao spreadsheetDao;
    @Autowired
    private OneClickTermDao oneClickTermDao;
    @Autowired
    private SpreadsheetRowHeaderDao spreadsheetRowHeaderDao;
    @Autowired
    private SpreadsheetRowDao spreadsheetRowDao;
    @Autowired
    private SpreadsheetTempRowDao spreadsheetTempRowDao;
    @Autowired
    private StaffRecordDao staffRecordDao;
    @Autowired
    private SpreadsheetColumnDao spreadsheetColumnDao;
    @Autowired
    private RowHeaderColumnService rowHeaderColumnService;
    @Autowired
    private SpreadsheetRowService spreadsheetRowService;
    @Autowired
    private SpreadsheetTempRowService spreadsheetTempRowService;
    @Autowired
    private StaffRecordService staffRecordService;
    @Autowired
    private SpreadsheetService spreadsheetService;
    @Autowired
    private UserStaffRecordDao userStaffRecordDao;

    /*@Override
    public ExcelImportContext excelDataParse(SpreadsheetImportRequest spreadsheetImportRequest) {
        ExcelImportContext excelImportContext =
                new ExcelImportContext(spreadsheetImportRequest.getExcelData());
        // 初始化 excelImportContext
        initExcelImportContext(spreadsheetImportRequest, excelImportContext);

        // 处理表头
        doExcelRowHeader(excelImportContext);

        // 处理行数据
        doSpreadsheetRow(excelImportContext);

        // 员工名录
        checkStaffRecord(excelImportContext);
        excelImportContext.setExcelRows(null);
        return excelImportContext;
    }*/

    @Override
    public ExcelImportContext excelDataParse(SpreadsheetImportRequest spreadsheetImportRequest) {
        ExcelImportContext excelImportContext =
                new ExcelImportContext(spreadsheetImportRequest.getExcelData());
        // 初始化 excelImportContext
        initExcelImportContext(spreadsheetImportRequest, excelImportContext);

        // 处理表头
        doExcelRowHeader(excelImportContext);

        // 处理行数据
        doSpreadsheetRow(excelImportContext);

        return excelImportContext;
    }

    /**
     * 初始化
     *
     * @param spreadsheetImportRequest
     * @param excelImportContext
     */
    private void initExcelImportContext(SpreadsheetImportRequest spreadsheetImportRequest, ExcelImportContext excelImportContext) {
        String companyId = spreadsheetImportRequest.getCompanyId();
        String termId = spreadsheetImportRequest.getTermId();
        OneClickTermDto oneClickTermDto = oneClickTermDao.findOne(termId);
        if (oneClickTermDto == null) {
            throw new OneClickException("one_click_term_unlawful_request", "非法请求！");
        }
        excelImportContext.setCompanyId(companyId);
        excelImportContext.setTerm(BeanConvertUtils.convertFor(oneClickTermDto, new OneClickTerm()));
        excelImportContext.setTermId(termId);
        excelImportContext.setMonth(spreadsheetImportRequest.getMonth());
        excelImportContext.setWeChatNotice(spreadsheetImportRequest.getWeChatNotice());
        excelImportContext.setEmailNotice(spreadsheetImportRequest.getEmailNotice());
        excelImportContext.setSmsNotice(spreadsheetImportRequest.getSmsNotice());
        SpreadsheetDto spreadsheetDto = new SpreadsheetDto();
        spreadsheetDto = BeanConvertUtils.convertFor(spreadsheetImportRequest, spreadsheetDto);
        excelImportContext.setSpreadsheet(BeanConvertUtils.convertFor(spreadsheetDto, new Spreadsheet()));
        //获取fixedColumnMete(包含姓名,电话号码,邮箱)  key:别名  value:RowHeaderColumn
        List<Map<String, RowHeaderColumn>> fixedColumns = rowHeaderColumnService.getExcelFieldMapping(termId);
        excelImportContext.setFixedColumns(fixedColumns);
    }

    /**
     * 封装Excel表头信息
     *
     * @param excelImportContext
     */
    private void doExcelRowHeader(ExcelImportContext excelImportContext) {
        //本次导入excel表头信息
        List<RowHeaderColumn> rowHeaderColumnList = new ArrayList<>();
        //本次导入excel对应表头固定字段
        Map<String, RowHeaderColumn> excelFixedColumnMap = new LinkedHashMap<>();
        // 获取Excel表头信息
        Map<String, Object> rowHeader = excelImportContext.getExcelRows().get(0);
        // 获取配置表头信息
        List<Map<String, RowHeaderColumn>> fixedColumns = excelImportContext.getFixedColumns();
        /*for (int i = 0; i < fixedColumns.size(); i++) {
            List<String> columnNames = Lists.newArrayList(fixedColumns.get(i).keySet());
            for (String columnName : columnNames) {
                //得到固定字段
                if (rowHeader.containsKey(columnName)) {
                    fixedColumns.get(i).get(columnName).setColumnDisplayName(columnName);
                    excelFixedColumnMap.put(columnName, fixedColumns.get(i).get(columnName));
                    break;
                }
            }
        }*/
        for (Map<String, RowHeaderColumn> fixedColumn : fixedColumns) {
            boolean isContinue = false;
            List<String> columnNames = Lists.newArrayList(fixedColumn.keySet());
            //不区分大小写匹配,得到固定字段map
            for (String columnName : columnNames) {
                for (String header : rowHeader.keySet()) {
                    String headerOld = header;
                    String headerNew = headerOld.replace(" ","");
                    if (header.equalsIgnoreCase("rowNum")) {
                        continue;
                    }
                    if (columnName.equalsIgnoreCase(headerNew)) {
                        fixedColumn.get(columnName).setColumnDisplayName(header);
                        excelFixedColumnMap.put(header, fixedColumn.get(columnName));
                        isContinue = true;
                        break;
                    }
                }
                if (isContinue) {
                    break;
                }
            }
        }
        //将Excel表头排序（姓名 1/手机号 2/邮箱 3）
        List<String> rowHeaderList = Lists.newArrayList();
        for (String rowHeaders : rowHeader.keySet()) {
            if (rowHeaders.equalsIgnoreCase("rowNum")) {
                continue;
            }
            rowHeaderList.add(rowHeaders);
        }
        List<String> fixedColumnList = Lists.newArrayList();//固定字段
        List<String> totalFieldColumn = Lists.newArrayList();
        for (String excelFixedColumn : excelFixedColumnMap.keySet()) {
            for (String excelColumn : rowHeaderList) {
                if (excelFixedColumn.equals(excelColumn)) {
                    fixedColumnList.add(excelColumn);
                    break;
                }
            }
        }
        for (String fixedColumn : fixedColumnList) {//如果存在固定字段，先删除
            for (int count = 0; count < rowHeaderList.size(); count++) {
                if (rowHeaderList.get(count).equals(fixedColumn)) {
                    rowHeaderList.remove(count);
                    break;
                }
            }
        }
        totalFieldColumn.addAll(fixedColumnList);
        totalFieldColumn.addAll(rowHeaderList);
        //为表头赋对应的属性。
        int columnIndex = 0;
        for (String excelColumn : totalFieldColumn) {
            RowHeaderColumn column = new RowHeaderColumn();
            if (excelFixedColumnMap.containsKey(excelColumn)) {
                column = excelFixedColumnMap.get(excelColumn);
                column.setColumnIndex(columnIndex);
                columnIndex++;
            } else {
                if (excelColumn.equalsIgnoreCase("rowNum")) {
                    continue;
                }
                column.setColumnFieldName(excelColumn);
                column.setColumnDisplayName(excelColumn);
                column.setFixedColumn(false);
                column.setColumnIndex(columnIndex);
                columnIndex++;
            }
            rowHeaderColumnList.add(column);
        }
        SpreadSheetRowHeaderDto spreadSheetRowHeaderDto = new SpreadSheetRowHeaderDto();
        List<Object> excelHeadRow = Lists.newArrayList();
        for (RowHeaderColumn rowHeaderColumn : rowHeaderColumnList) {
            excelHeadRow.add(rowHeaderColumn.getColumnDisplayName());
        }
        //判断表头是否存在邮箱这个字段
        if (excelHeadRow.size() >= 3) {
            excelHeadRow.set(0, "姓名");
            excelHeadRow.set(1, "手机号");
            List<Map<String, RowHeaderColumn>> excelFieldMapping = rowHeaderColumnService.getExcelFieldMapping(excelImportContext.getTermId());
            Map<String, RowHeaderColumn> rowHeaderColumnMap = excelFieldMapping.get(2);
            for (String rowHeaderColumn : rowHeaderColumnMap.keySet()) {
                if (rowHeaderColumn.equalsIgnoreCase(excelHeadRow.get(2).toString().replace(" ",""))) {
                    excelHeadRow.set(2, "邮箱");
                    break;
                }
            }
        } else {
            excelHeadRow.set(0, "姓名");
            excelHeadRow.set(1, "手机号");
        }
        for (int count = 0; count < rowHeaderColumnList.size(); count++) {
            rowHeaderColumnList.get(count).setColumnDisplayName(excelHeadRow.get(count).toString());
        }
        spreadSheetRowHeaderDto.setData(rowHeaderColumnList);
        excelImportContext.setSpreadSheetRowHeader(BeanConvertUtils.convertFor(spreadSheetRowHeaderDto, new SpreadSheetRowHeader()));
        excelImportContext.setExcelFixedColumnMap(excelFixedColumnMap);
    }

    /**
     * 封裝Row
     *
     * @param excelImportContext
     */
    private void doSpreadsheetRow(ExcelImportContext excelImportContext) {
        int rowNo = 1;//代表行数
        int rows = 0;//代表excel导入进来行的开始
        int successCount = 0;//成功数
        int errorCount = 0;//错误数
        String termId = excelImportContext.getTermId();
        String companyId = excelImportContext.getCompanyId();
        Date month = excelImportContext.getMonth();
        List<Map> excelRows = excelImportContext.getExcelRows();//得到所有的Excel行数据
        List<String> errorList = Lists.newArrayList();//错误信息list
        List<SpreadsheetRow> spreadsheetRowList = Lists.newArrayList();//电子表行数据list
        ExcelImportResult importResult = new ExcelImportResult();
        Map<String, RowHeaderColumn> excelFixedColumnMap = excelImportContext.getExcelFixedColumnMap();//固定字段
        for (Map rowMap : excelRows) {//遍历Excel行数据
            SpreadsheetRow spreadsheetRow = new SpreadsheetRow();
            List<Object> fixedCellValueList = Lists.newArrayList();//存固定单元格list
            List<Object> nonFixedCellValueList = Lists.newArrayList();//存非固定单元格list
            List<Object> assembleFixedCellValueList = Lists.newArrayList();//固定和非固定值(包含固定字段非法数据)
            if (rowNo == 1) {
                rows = Integer.parseInt(rowMap.get("rowNum").toString());
            }
            Boolean errorSign = true;
            int fixedAdd = 1;
            //得到固定字段的值
            for (String excelFixed : excelFixedColumnMap.keySet()) {
                for (Object cellObject : rowMap.entrySet()) {//遍历excel单元格数据
                    Map.Entry<String, Object> cell = (Map.Entry<String, Object>) cellObject;
                    String excelColumnName = cell.getKey();//表头
                    Object cellValue = cell.getValue();//单元格
                    if (excelColumnName.equalsIgnoreCase("rowNum")) {
                        break;
                    }
                    String fieldValue = cellValue == null ? "" : cellValue + "";
                    if (excelColumnName.equalsIgnoreCase(excelFixed)) {
                        RowHeaderColumn rowHeaderColumn = excelFixedColumnMap.get(excelColumnName);
                        if (StringUtils.isBlank(fieldValue) && !excelFixedColumnMap.get(excelFixed).getColumnFieldMeta().equals(SpreadsheetRowColumnMeta.EMAIL_ADDRESS)) {
                            errorList.add("第" + (rows) + "行," + excelColumnName + "不能为空！");
                            errorSign = false;
                            // TODO 手机号校验
                        } else if (excelFixedColumnMap.get(excelFixed).getColumnFieldMeta().equals(SpreadsheetRowColumnMeta.MOBILE_NO)) {
                            if (fieldValue.length() != 11) {
                                errorList.add("第" + (rows) + "行,手机号格式不正确！");
                                errorSign = false;
                            }
                            // TODO 邮件校验
                        } else if (excelFixedColumnMap.get(excelFixed).getColumnFieldMeta().equals(SpreadsheetRowColumnMeta.EMAIL_ADDRESS) && excelImportContext.getEmailNotice()) {
                            if (StringUtils.isBlank(fieldValue)) {
                                fixedCellValueList.add(fieldValue);
                                continue;
                            }
                            if (!ExcelImportValidator.checkEmail(fieldValue)) {
                                errorList.add("第" + (rows) + "行,邮件格式不正确！");
                                errorSign = false;
                            }
                        }
                        fixedCellValueList.add(fieldValue);
                        //往行的姓名、手机号、邮箱赋值
                        if (fixedAdd == 1) {
                            spreadsheetRow.setUserName(fieldValue);
                        } else if (fixedAdd == 2) {
                            spreadsheetRow.setMobileNo(fieldValue);
                        } else {
                            spreadsheetRow.setEmail(fieldValue);
                        }
                        setEntityFieldValue(SpreadsheetRow.class, spreadsheetRow, rowHeaderColumn.getColumnFieldName(), fieldValue);
                        fixedAdd++;
                        break;
                    }
                }
            }
            //得到非固定字段的值
            for (Object cellObject : rowMap.entrySet()) {
                Map.Entry<String, Object> cell = (Map.Entry<String, Object>) cellObject;
                String excelColumnName = cell.getKey();//表头
                Object cellValue = cell.getValue();//单元格
                if (excelColumnName.equalsIgnoreCase("rowNum")) {
                    break;
                }
                String fieldValue = cellValue == null ? "" : cellValue + "";
                if (excelFixedColumnMap.containsKey(excelColumnName)) {
                    continue;
                }
                nonFixedCellValueList.add(fieldValue);
            }
            assembleFixedCellValueList.addAll(fixedCellValueList);
            assembleFixedCellValueList.addAll(nonFixedCellValueList);
            //固定字段存在无效值
            if (!errorSign) {
                rows++;
                rowNo++;
                continue;
            } else {
                spreadsheetRow.setTermId(termId);
                spreadsheetRow.setCompanyId(companyId);
                spreadsheetRow.setMonth(month);
                spreadsheetRow.setRowIndex(rowNo);
                spreadsheetRow.setCreatedAt(new Date());
                spreadsheetRow.setPublishStatus(false);
                spreadsheetRow.setData(assembleFixedCellValueList);
                spreadsheetRowList.add(spreadsheetRow);
            }
            rows++;
            rowNo++;
        }
        errorCount = excelRows.size() - spreadsheetRowList.size();//失败数
        successCount = spreadsheetRowList.size();//成功数
        importResult.setErrorList(errorList);//错误信息
        importResult.setErrorCount(errorCount);
        importResult.setSuccessCount(successCount);
        excelImportContext.getSpreadsheet().setRowCount(successCount);
        excelImportContext.setExcelSpreadsheetRowList(spreadsheetRowList);
        excelImportContext.setExcelImportResult(importResult);

    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public ExcelImportResult saveExcelData(ExcelImportContext excelImportContext) {
        //电子表格
        SpreadsheetDto spreadsheetDto = BeanConvertUtils.convertFor(excelImportContext.getSpreadsheet(), new SpreadsheetDto());
        SpreadSheetRowHeaderDto spreadSheetRowHeaderDto;
        OneClickTermDto termDto = BeanConvertUtils.convertFor(excelImportContext.getTerm(), new OneClickTermDto());
        spreadsheetDto.setTerm(termDto);
        spreadsheetDto.setCreatedAt(new Date());
        spreadsheetDto.setStatus(1);//默认为正式数据
        spreadsheetDto.setRowCount(excelImportContext.getExcelSpreadsheetRowList().size());
        spreadsheetDao.save(spreadsheetDto);//保存电子表格

        //固定列
        List<SpreadsheetColumnDto> spreadsheetColumnDtoList = new ArrayList<>();
        for (RowHeaderColumn rowHeaderColumn : excelImportContext.getExcelFixedColumnMap().values()) {
            SpreadsheetColumnDto spreadsheetColumnDto = BeanConvertUtils.convertFor(rowHeaderColumn, new SpreadsheetColumnDto());
            spreadsheetColumnDto.setSpreadsheetId(spreadsheetDto.getId());
            spreadsheetColumnDtoList.add(spreadsheetColumnDto);
        }
        spreadsheetColumnDao.save(spreadsheetColumnDtoList);//保存固定列

        spreadSheetRowHeaderDto = BeanConvertUtils.convertFor(excelImportContext.getSpreadSheetRowHeader(), new SpreadSheetRowHeaderDto());
        spreadSheetRowHeaderDto.setCreatedAt(new Date());
        spreadSheetRowHeaderDto.setSpreadsheetId(spreadsheetDto.getId());
        spreadsheetRowHeaderDao.save(spreadSheetRowHeaderDto);//保存表头

        //行数据
        List<SpreadsheetRowDto> spreadsheetRowDtoList = new ArrayList<>();
        for (SpreadsheetRow spreadsheetRow : excelImportContext.getExcelSpreadsheetRowList()) {
            SpreadsheetRowDto spreadsheetRowDto = BeanConvertUtils.convertFor(spreadsheetRow, new SpreadsheetRowDto());
            spreadsheetRowDto.setRowHeader(spreadSheetRowHeaderDto);
            spreadsheetRowDto.setSpreadsheet(spreadsheetDto);
            spreadsheetRowDto.setPublishStatus(false);
            spreadsheetRowDtoList.add(spreadsheetRowDto);
        }
        spreadsheetRowDao.save(spreadsheetRowDtoList);//保存行数据
        ExcelImportResult excelImportResult = new ExcelImportResult();
        excelImportResult.setSpreadsheetId(spreadsheetDto.getId());
        return excelImportResult;
    }


    /**
     * 初始化
     *
     * @param spreadsheetImportRequest request
     * @param excelImportContext       context
     */
    /*private void initExcelImportContext(SpreadsheetImportRequest spreadsheetImportRequest, ExcelImportContext excelImportContext) {
        String companyId = spreadsheetImportRequest.getCompanyId();
        String termId = spreadsheetImportRequest.getTermId();
        String spreadSheetId = spreadsheetImportRequest.getSpreadSheetId();
        OneClickTermDto oneClickTermDto = oneClickTermDao.findOne(termId);
        if (oneClickTermDto == null) {
            throw new OneClickException("one_click_term_unlawful_request", "非法请求！");
        }
        excelImportContext.setCompanyId(companyId);
        excelImportContext.setTerm(BeanConvertUtils.convertFor(oneClickTermDto, new OneClickTerm()));
        excelImportContext.setSpreadsheetId(spreadSheetId);
        excelImportContext.setMonth(spreadsheetImportRequest.getMonth());
        excelImportContext.setSecondImport(spreadsheetImportRequest.getIsSecondImport() != null && spreadsheetImportRequest.getIsSecondImport());
        SpreadsheetDto spreadsheetDto = new SpreadsheetDto();
        //第一次导入从spreadsheetImportRequest获取电子表格，否则从spreadsheetDao中获取。
        spreadsheetDto = excelImportContext.isSpreadsheetNew() ? BeanConvertUtils.convertFor(spreadsheetImportRequest, spreadsheetDto) :
                spreadsheetDao.findOne(spreadSheetId);
        excelImportContext.setSpreadsheet(BeanConvertUtils.convertFor(spreadsheetDto, new Spreadsheet()));
        //1. 获取fixed column meta 第一次导入从termMeta中获取，否则获取对应的SpreadsheetColumnDto
        List<RowHeaderColumn> rowHeaderColumnList = excelImportContext.isSpreadsheetNew() ? rowHeaderColumnService.getTermFixedColumnMetaList(termId) :
                rowHeaderColumnService.spreadsheetColumnDtoListConvertColumnList(spreadsheetDto.getColumns());
        Map<String, RowHeaderColumn> spreadFixedColumnMap = new HashMap<>();//固定字段map   key:columnFieldMeta  value:RowHeaderColumn
        Map<String, RowHeaderColumn> i18nFixedColumnMap = new HashMap<>();//i18n所有国际化列名所对应的固定列  key:字段名（columnDisplayName\columnFieldMeta） value:RowHeaderColumn
        Map<String, Object[]> i18nKeyMap = new HashMap<>();
        rowHeaderColumnList.forEach(column -> {
            i18nKeyMap.put(column.getColumni8nKey(), null);
            spreadFixedColumnMap.put(column.getColumnFieldMeta(), column);
        });
        // 获取i18nKey 对应的所有国际化文本
        Map<String, List<String>> i18nMessageMap = messageService.messagesMap(i18nKeyMap);
        for (RowHeaderColumn rowHeaderColumn : rowHeaderColumnList) {
            String i18nKey = rowHeaderColumn.getColumni8nKey();
            List<String> messageList = i18nMessageMap.get(rowHeaderColumn.getColumni8nKey());
            if (!CollectionUtils.isEmpty(messageList)) {
                if (messageList.size() == 1 && i18nKey.equals(messageList.get(0))) {
                    i18nFixedColumnMap.put(rowHeaderColumn.getColumnDisplayName(), rowHeaderColumn);
                    continue;
                }
                messageList.forEach(message ->
                        i18nFixedColumnMap.put(message, rowHeaderColumn)
                );
                continue;
            }
            i18nFixedColumnMap.put(rowHeaderColumn.getColumnDisplayName(), rowHeaderColumn);
        }
        excelImportContext.setI18nFixedColumnMap(i18nFixedColumnMap);
        excelImportContext.setSpreadFixedColumnMap(spreadFixedColumnMap);

        Map<String, IdCardType> idCardTypeMap = new HashMap<>();
        List<IdCardType> idCardTypeList = Arrays.asList(IdCardType.values());
        idCardTypeList.forEach(idCardType -> {
            idCardTypeMap.put(idCardType.getName(), idCardType);
        });
        excelImportContext.setIdCardTypeMap(idCardTypeMap);
    }*/

    /**
     * 封装Excel表头信息
     *
     * @param excelImportContext context
     */
    /*private void doExcelRowHeader(ExcelImportContext excelImportContext) {
        List<RowHeaderColumn> rowHeaderColumnList = new ArrayList<>();//本次导入excel表头信息
        Map<String, RowHeaderColumn> excelFixedColumnMap = new HashMap<>();//导入excel对应的固定列  key:列名 value：RowHeaderColumn
        int columnIndex = 0;
        for (Object key : excelImportContext.getExcelRows().get(0).keySet()) {
            String columnName = (String) key;
            RowHeaderColumn column = new RowHeaderColumn();
            // 如果列头名称能匹配上，则为固定列
            if (excelImportContext.getI18nFixedColumnMap().containsKey(columnName)) {
                column = excelImportContext.getI18nFixedColumnMap().get(columnName);
                column.setFixedColumn(true);
                excelFixedColumnMap.put(columnName, column);
            } else {
                column.setColumnFieldName(columnName);
                column.setColumnDisplayName(columnName);
                column.setFixedColumn(false);
            }

            // 如果是固定字段，校验其位置是否于模板位置一致
            if (column.isFixedColumn() && columnIndex != column.getColumnIndex()) {
                throw new OneClickException("one_click_term_template_head_not_allowed_to_change", "模板表头不允许改变位置");
            } else {
                column.setColumnIndex(columnIndex);
            }
            rowHeaderColumnList.add(column);
            columnIndex++;
        }
        // 如果不是初次导入，校验表头是否于第一次导入的一致
        SpreadSheetRowHeaderDto spreadSheetRowHeaderDto = new SpreadSheetRowHeaderDto();
        String columnJsonArraySerialize = JSONArray.toJSONString(rowHeaderColumnList);
        String md5sum = MD5Utils.MD5(columnJsonArraySerialize);
        if (!excelImportContext.isSpreadsheetNew()) {
            spreadSheetRowHeaderDto = spreadsheetRowHeaderDao.findBySpreadsheetId(excelImportContext.getSpreadsheetId());
            // 通过MD5验证表头是否修改过
            if (!md5sum.equals(spreadSheetRowHeaderDto.getMd5Sum())) {
                throw new OneClickException("one_click_spreadsheet_row_header_differ", "表头不一致，请检查后重新导入！");
            }
        } else {
            //spreadSheetRowHeaderDto.setDataStr(columnJsonArraySerialize);
            spreadSheetRowHeaderDto.setData(rowHeaderColumnList);
            spreadSheetRowHeaderDto.setMd5Sum(md5sum);
        }
        excelImportContext.setExcelFixedColumnMap(excelFixedColumnMap);//导入excel对应的固定列  key:列名 value：RowHeaderColumn
        excelImportContext.setSpreadSheetRowHeader(BeanConvertUtils.convertFor(spreadSheetRowHeaderDto, new SpreadSheetRowHeader()));//表头所有信息
    }*/

    /**
     * 封装Excel row
     *
     * @param excelImportContext context
     */
    /*private void doSpreadsheetRow(ExcelImportContext excelImportContext) {
        // 获取公司所有员工名录信息
        List<StaffRecord> companyAllStaffRecord = staffRecordService.getCompanyAllStaffRecord(excelImportContext.getCompanyId());
        //公司所有员工名录 身份证号：员工名录
        Map<String, StaffRecord> companyAllStaffRecordMap = new HashMap<>();
        //公司所有员工 员工ID：员工名录
        Map<String, StaffRecord> companyAllStaffRecordIdAndStaffRecordMap = new HashMap<>();
        //公司所有员工身份证号  手机号：身份证号
        Map<String, String> companyAllStaffRecordIdCardNoMobileNoMap = new HashMap<>();
        for (StaffRecord staffRecord : companyAllStaffRecord) {
            companyAllStaffRecordMap.put(staffRecord.getIdCardNo(), staffRecord);
            companyAllStaffRecordIdAndStaffRecordMap.put(staffRecord.getId(), staffRecord);
            companyAllStaffRecordIdCardNoMobileNoMap.put(staffRecord.getMobileNo(), staffRecord.getIdCardNo());
        }
        excelImportContext.setCompanyAllStaffRecordIdCardNoMobileNoMap(companyAllStaffRecordIdCardNoMobileNoMap);
        excelImportContext.setCompanyAllStaffRecordMap(companyAllStaffRecordMap);

        Map<String, SpreadsheetRow> existStaffRowMap = new HashMap<>();
        Map<String, String> existStaffMobileNo = new HashMap<>();
        // 如果是编辑，则获取已经导入的row， 以员工身份作为key 员工身份： 证件类型+证件号确认为一个人
        if (!excelImportContext.isSpreadsheetNew()) {
            List<SpreadsheetRow> existRowList = excelImportContext.isSecondImport() ?
                    spreadsheetTempRowService.getSpreadsheetRowBySpreadsheetId(excelImportContext.getSpreadsheetId()) :
                    spreadsheetRowService.getSpreadsheetRowBySpreadsheetId(excelImportContext.getSpreadsheetId());
            excelImportContext.setExistSpreadsheetRowList(existRowList);
            for (SpreadsheetRow spreadsheetRow : existRowList) {
                if (spreadsheetRow.getStaffRecordId() == null) {
                    String staffIdentityKey = spreadsheetRow.getIdCardNo();
                    existStaffRowMap.put(staffIdentityKey, spreadsheetRow);
                    existStaffMobileNo.put(spreadsheetRow.getMobileNo(), staffIdentityKey);
                    continue;
                }
                StaffRecord staffRecord = companyAllStaffRecordIdAndStaffRecordMap.get(spreadsheetRow.getStaffRecordId());
                existStaffRowMap.put(staffRecord.getIdCardNo(), spreadsheetRow);
                existStaffMobileNo.put(staffRecord.getMobileNo(), staffRecord.getIdCardNo());
            }

        }

        ExcelImportResult importResult = new ExcelImportResult();
        int rowNo = 0;//行数
        int newRowCount = 0;//新创建数
        int updateRowCount = 0;//修改数
        int staffRecordMobileNoUpdateCount = 0;//员工手机号修改数
        int notAllowPublishCount = 0;//不允许发布总数
        String termId = excelImportContext.getTerm().getId();
        String companyId = excelImportContext.getCompanyId();
        List<StaffSpreadsheetRow> staffSpreadsheetRowList = new ArrayList<>();
        List<String> mobileNoList = new ArrayList<>();
        List<String> rowStaffIdentityKeyList = new ArrayList<>();
        Date month = excelImportContext.getMonth();
        List<Map> excelRows = excelImportContext.getExcelRows();
        for (Map rowMap : excelRows) {
            StaffSpreadsheetRow staffSpreadsheetRow = new StaffSpreadsheetRow();//导入的row list
            StaffRecord staffRecord = new StaffRecord();
            SpreadsheetRow spreadsheetRow = new SpreadsheetRow();
            int cellNo = 0;
            List<Object> flexibleCellValueList = new ArrayList<>();
            for (Object cellObject : rowMap.entrySet()) {
                Map.Entry<String, Object> cell = (Map.Entry<String, Object>) cellObject;
                String excelColumnName = cell.getKey();
                Object cellValue = cell.getValue();
                Object fieldValue;
                if (excelImportContext.getI18nFixedColumnMap().containsKey(excelColumnName)) {//判断是否为固定列
                    RowHeaderColumn column = excelImportContext.getI18nFixedColumnMap().get(excelColumnName);
                    if (column.getRequired() && cellValue == null) {//如果为固定列，则必填
                        //TODO cell 不能为空日志处理
                        throw new OneClickException("one_click_term_import_data_cell_is_null", "第" + (rowNo + 2) + "行,第" + (cellNo + 1) + "单元格内容不能为空！", new Object[]{(rowNo + 2), (cellNo + 1)});
                    }
                    String columnFieldName = column.getColumnFieldName();
                    if (RowHeaderColumn.ColumnType.DATE == column.getColumnType()) {//判断是否为时间类型
                        if (cellValue instanceof Date) {
                            fieldValue = DateUtils.formatDateStr((Date) cellValue, DateUtils.STANDARD_DATE_FORMAT_YMDHM);
                        } else {
                            fieldValue = cellValue.toString();
                        }
                    } else {
                        fieldValue = cellValue.toString();
                    }
                    switch (column.getColumnFieldMeta()) {
                        case SpreadsheetRowColumnMeta.MOBILE_NO:
                            String mobileNo = fieldValue.toString();
                            ExcelImportValidator.validateMobileNo(mobileNo);
                            if (mobileNoList.contains(mobileNo)) {
                                throw new OneClickException("one_click_term_mobile_is_repeat", "手机号码：" + mobileNo + "重复！", new Object[]{mobileNo});
                            }
                            staffRecord.setMobileNo(mobileNo);
                            mobileNoList.add(mobileNo);
                            break;
                        case SpreadsheetRowColumnMeta.IDCARD_NO:
                            staffRecord.setIdCardNo(fieldValue.toString().toUpperCase());
                            break;
                        case SpreadsheetRowColumnMeta.STAFF_NAME:
                            staffRecord.setStaffName(fieldValue.toString());
                            break;
                        case SpreadsheetRowColumnMeta.ALLOW_PUBLISH:
                            String allowPublish = fieldValue.toString();
                            if ("是".equals(allowPublish) || "YES".equals(allowPublish.toUpperCase())) {
                                fieldValue = true;
                            } else if ("否".equals(allowPublish) || "NO".equals(allowPublish.toUpperCase())) {
                                notAllowPublishCount++;
                                fieldValue = false;
                            } else {
                                throw new OneClickException("one_click_term_allow_publish_not_correct", "是否允许发布输入格式有误!");
                            }
                            break;
                        default:
                            break;
                    }
                    setEntityFieldValue(SpreadsheetRow.class, spreadsheetRow, columnFieldName, fieldValue);
                } else {
                    fieldValue = cellValue != null ? cellValue.toString() : "";
                }
                flexibleCellValueList.add(fieldValue);
                cellNo++;
            }
            spreadsheetRow.setTermId(termId);
            spreadsheetRow.setCompanyId(companyId);
            //spreadsheetRow.setDataStr(JSONArray.toJSONString(flexibleCellValueList));
            spreadsheetRow.setData(flexibleCellValueList);
            spreadsheetRow.setMonth(month);
            String staffIdentityKey = staffRecord.getIdCardNo();

            //判读本次导入数据员工是否重复
            if (rowStaffIdentityKeyList.contains(staffIdentityKey)) {
                throw new OneClickException("one_click_term_import_data_id_card_no_repeat", "证件号：" + staffRecord.getIdCardNo() + "重复！");
            }
            rowStaffIdentityKeyList.add(staffIdentityKey);

            // 更新row
            if (!excelImportContext.isSpreadsheetNew() && existStaffRowMap.containsKey(staffIdentityKey)) {
                SpreadsheetRow exitSpreadsheetRow = existStaffRowMap.get(staffIdentityKey);
                spreadsheetRow.setId(exitSpreadsheetRow.getId());
                spreadsheetRow.setPublishStatus(false);
                spreadsheetRow.setStaffRecordId(exitSpreadsheetRow.getStaffRecordId());
                spreadsheetRow.setCreatedAt(exitSpreadsheetRow.getCreatedAt());
                spreadsheetRow.setUpdatedAt(new Date());
                spreadsheetRow.setRowIndex(exitSpreadsheetRow.getRowIndex());
                updateRowCount++;
            } else {
                spreadsheetRow.setRowIndex(rowNo);
                spreadsheetRow.setCreatedAt(new Date());
                spreadsheetRow.setPublishStatus(false);
                newRowCount++;
            }
            spreadsheetRow.setIdCardNo(staffRecord.getIdCardNo());
            spreadsheetRow.setMobileNo(staffRecord.getMobileNo());
            // 手机号码重复
            if (existStaffMobileNo.containsKey(staffRecord.getMobileNo())) {
                if (!staffIdentityKey.equals(existStaffMobileNo.get(staffRecord.getMobileNo()))) {
                    throw new OneClickException("one_click_term_mobile_is_repeat", "手机号码：" + staffRecord.getMobileNo() + "已存在", new Object[]{staffRecord.getMobileNo()});
                } else {
                    existStaffMobileNo.put(staffRecord.getMobileNo(), staffRecord.getIdCardNo());
                }
            }

            if (companyAllStaffRecordMap.containsKey(staffIdentityKey)) {
                if (!Objects.equals(companyAllStaffRecordMap.get(staffIdentityKey).getMobileNo(), staffRecord.getMobileNo())) {
                    // 员工名录手机号码更新了
                    staffRecordMobileNoUpdateCount++;
                }
            }

            staffSpreadsheetRow.setStaffRecord(staffRecord);
            staffSpreadsheetRow.setSpreadsheetRow(spreadsheetRow);
            staffSpreadsheetRowList.add(staffSpreadsheetRow);
            rowNo++;
        }
        excelImportContext.setStaffSpreadsheetRowList(staffSpreadsheetRowList);
        excelImportContext.setSpreadsheetRowCount(newRowCount);
        excelImportContext.setSpreadsheetNotAllowPublishRowCount(notAllowPublishCount);
        if (excelImportContext.isSpreadsheetNew()) {
            excelImportContext.getSpreadsheet().setMonth(month);
        }
        importResult.setSuccessCount(rowNo);
        importResult.setNewCount(newRowCount);
        importResult.setUpdateCount(updateRowCount);
        importResult.setStaffRecordMobileNoUpdateCount(staffRecordMobileNoUpdateCount);
        excelImportContext.setExcelImportResult(importResult);
    }*/

    /**
     * 证件类型+证件号码确定一个人，并且不允许修改
     */
    private void checkStaffRecord(ExcelImportContext excelImportContext) {
        Map<String, StaffRecord> companyAllStaffRecordMap = excelImportContext.getCompanyAllStaffRecordMap();
        List<StaffRecord> newStaffList = new ArrayList<>();
        Map<String, String> companyAllStaffRecordIdCardNoMobileNoMap = excelImportContext.getCompanyAllStaffRecordIdCardNoMobileNoMap();
        for (StaffSpreadsheetRow staffSpreadsheetRow : excelImportContext.getStaffSpreadsheetRowList()) {
            StaffRecord staffRecord = staffSpreadsheetRow.getStaffRecord();
            String staffIdentityKey = staffRecord.getIdCardNo();

            //判断手机号码是不是被其他员工使用
            if (companyAllStaffRecordIdCardNoMobileNoMap.containsKey(staffRecord.getMobileNo()) &&
                    !Objects.equals(companyAllStaffRecordIdCardNoMobileNoMap.get(staffRecord.getMobileNo()), staffIdentityKey)) {
                throw new OneClickException("one_click_term_mobile_is_repeat", "手机号码：" + staffRecord.getMobileNo() + "已经被占用", new Object[]{staffRecord.getMobileNo()});
            }
            if (companyAllStaffRecordMap.containsKey(staffIdentityKey)) {
                staffRecord = companyAllStaffRecordMap.get(staffRecord.getIdCardNo());
                staffSpreadsheetRow.getSpreadsheetRow().setStaffRecordId(staffRecord.getId());
            } else {
                //封装需要新增的员工名录
                staffRecord.setCompanyId(excelImportContext.getCompanyId());
                staffRecord.setAllow(true);
                staffRecord.setCreatedAt(new Date());
                newStaffList.add(staffRecord);
            }
        }
        excelImportContext.setCompanyAllStaffRecordIdCardNoMobileNoMap(null);
        excelImportContext.getExcelImportResult().setStaffRecordNewCount(newStaffList.size());
        excelImportContext.setNewStaffList(newStaffList);
    }


    /*@Transactional(rollbackFor = Exception.class)
    @Override
    public Spreadsheet saveExcelData(ExcelImportContext excelImportContext) {
        SpreadsheetDto spreadsheetDto = BeanConvertUtils.convertFor(excelImportContext.getSpreadsheet(), new SpreadsheetDto());
        SpreadSheetRowHeaderDto spreadSheetRowHeaderDto;
        OneClickTermDto termDto = BeanConvertUtils.convertFor(excelImportContext.getTerm(), new OneClickTermDto());
        if (excelImportContext.isSpreadsheetNew()) {
            // 新增
            spreadsheetDto.setCreatedAt(new Date());
            spreadsheetDto.setTerm(termDto);
            spreadsheetDao.save(spreadsheetDto);

            // 固定列
            List<SpreadsheetColumnDto> spreadsheetColumnDtoList = new ArrayList<>();
            for (RowHeaderColumn rowHeaderColumn : excelImportContext.getExcelFixedColumnMap().values()) {
                SpreadsheetColumnDto spreadsheetColumnDto = BeanConvertUtils.convertFor(rowHeaderColumn, new SpreadsheetColumnDto());
                spreadsheetColumnDto.setSpreadsheetId(spreadsheetDto.getId());
                spreadsheetColumnDtoList.add(spreadsheetColumnDto);
            }
            spreadsheetColumnDao.save(spreadsheetColumnDtoList);
            spreadSheetRowHeaderDto = BeanConvertUtils.convertFor(excelImportContext.getSpreadSheetRowHeader(), new SpreadSheetRowHeaderDto());
            spreadSheetRowHeaderDto.setCreatedAt(new Date());
            spreadSheetRowHeaderDto.setSpreadsheetId(spreadsheetDto.getId());
            spreadsheetRowHeaderDao.save(spreadSheetRowHeaderDto);
        } else {
            spreadSheetRowHeaderDto = spreadsheetRowHeaderDao.findBySpreadsheetId(spreadsheetDto.getId());
        }

        // TODO 与AppUser  建立关联

        //添加员工名录
        List<StaffRecordDto> newStaffList = new ArrayList<>();
        excelImportContext.getNewStaffList().forEach(staffRecord ->
                newStaffList.add(BeanConvertUtils.convertFor(staffRecord, new StaffRecordDto()))
        );
        staffRecordDao.save(newStaffList);
        Map<String, String> identityStaffRecordIdMap = newStaffList.stream().collect(
                Collectors.toMap(staffRecordDto -> staffRecordDto.getIdCardNo(), StaffRecordDto::getId));
        List<SpreadsheetRowDto> spreadsheetRowDtoList = new ArrayList<>();
        for (StaffSpreadsheetRow staffSpreadsheetRow : excelImportContext.getStaffSpreadsheetRowList()) {
            SpreadsheetRowDto spreadsheetRowDto = BeanConvertUtils.convertFor(staffSpreadsheetRow.getSpreadsheetRow(), new SpreadsheetRowDto());
            String staffIdentityKey = staffSpreadsheetRow.getStaffRecord().getIdCardNo();
            if (spreadsheetRowDto.getStaffRecordId() == null) {
                spreadsheetRowDto.setStaffRecordId(identityStaffRecordIdMap.get(staffIdentityKey));
            }
            spreadsheetRowDto.setRowHeader(spreadSheetRowHeaderDto);
            spreadsheetRowDto.setSpreadsheet(spreadsheetDto);
            // 默认发布状态
            spreadsheetRowDto.setPublishStatus(false);
            spreadsheetRowDtoList.add(spreadsheetRowDto);
        }
        spreadsheetRowDao.save(spreadsheetRowDtoList);
        if (!excelImportContext.isSpreadsheetNew()) {
            spreadsheetDto.setUpdatedAt(new Date());
            spreadsheetDao.save(spreadsheetDto);
        }
        //重新计算保存合计数据
        spreadsheetService.saveFootData(spreadsheetDto.getId());

        return BeanConvertUtils.convertFor(spreadsheetDto, new Spreadsheet());
    }*/


    @Transactional(rollbackFor = Exception.class)
    @Override
    public ExcelImportResult saveExcelTempData(ExcelImportContext excelImportContext) {
        SpreadsheetDto spreadsheetDto = BeanConvertUtils.convertFor(excelImportContext.getSpreadsheet(), new SpreadsheetDto());
        SpreadSheetRowHeaderDto spreadSheetRowHeaderDto;
        OneClickTermDto termDto = BeanConvertUtils.convertFor(excelImportContext.getTerm(), new OneClickTermDto());
        Date nowDate = new Date();
        if (excelImportContext.isSpreadsheetNew()) {
            spreadsheetDto.setStatus(0);
            spreadsheetDto.setTerm(termDto);
            spreadsheetDto.setCreatedAt(nowDate);
            UserSpreadsheetDto owner = new UserSpreadsheetDto();
            owner.setUserId(excelImportContext.getUserId());
            owner.setSpreadsheet(spreadsheetDto);
            owner.setCreatedAt(nowDate);
            spreadsheetDto.setOwner(owner);
            spreadsheetDto.setTerm(termDto);
            spreadsheetDao.save(spreadsheetDto);

            // 固定列
            List<SpreadsheetColumnDto> spreadsheetColumnDtoList = new ArrayList<>();
            for (RowHeaderColumn rowHeaderColumn : excelImportContext.getExcelFixedColumnMap().values()) {
                SpreadsheetColumnDto spreadsheetColumnDto = BeanConvertUtils.convertFor(rowHeaderColumn, new SpreadsheetColumnDto());
                spreadsheetColumnDto.setSpreadsheetId(spreadsheetDto.getId());
                spreadsheetColumnDtoList.add(spreadsheetColumnDto);
            }
            spreadsheetColumnDao.save(spreadsheetColumnDtoList);
            spreadSheetRowHeaderDto = BeanConvertUtils.convertFor(excelImportContext.getSpreadSheetRowHeader(), new SpreadSheetRowHeaderDto());
            spreadSheetRowHeaderDto.setCreatedAt(nowDate);
            spreadSheetRowHeaderDto.setSpreadsheetId(spreadsheetDto.getId());
            spreadsheetRowHeaderDao.save(spreadSheetRowHeaderDto);
        } else {
            spreadSheetRowHeaderDto = spreadsheetRowHeaderDao.findBySpreadsheetId(spreadsheetDto.getId());
        }
        // 保存草稿 row
        List<String> updateSpreadsheetRowId = new ArrayList<>();
        List<SpreadsheetTempRowDto> spreadsheetRowDtoList = new ArrayList<>();
        for (StaffSpreadsheetRow staffSpreadsheetRow : excelImportContext.getStaffSpreadsheetRowList()) {
            SpreadsheetRow spreadsheetRow = staffSpreadsheetRow.getSpreadsheetRow();
            SpreadsheetTempRowDto spreadsheetTempRowDto = BeanConvertUtils.convertFor(spreadsheetRow, new SpreadsheetTempRowDto());
            if (spreadsheetRow.getId() != null) {
                if (!excelImportContext.isSecondImport()) {
                    spreadsheetTempRowDto.setSpreadsheetRowId(spreadsheetRow.getId());
                }
                updateSpreadsheetRowId.add(spreadsheetRow.getId());
            }
            spreadsheetTempRowDto.setId(null);
            spreadsheetTempRowDto.setSpreadsheet(spreadsheetDto);
            spreadsheetTempRowDto.setRowHeader(spreadSheetRowHeaderDto);
            spreadsheetRowDtoList.add(spreadsheetTempRowDto);
        }

        // 如果不是新增，需要将已经导入过并且没有更新过的row 储存到临时表
        if (!excelImportContext.isSpreadsheetNew()) {
            excelImportContext.getExistSpreadsheetRowList().stream().filter(spreadsheetRow -> !updateSpreadsheetRowId.contains(spreadsheetRow.getId())).forEach(spreadsheetRow -> {
                SpreadsheetTempRowDto spreadsheetTempRowDto = BeanConvertUtils.convertFor(spreadsheetRow, new SpreadsheetTempRowDto());
                spreadsheetTempRowDto.setId(null);
                spreadsheetTempRowDto.setSpreadsheet(spreadsheetDto);
                spreadsheetTempRowDto.setRowHeader(spreadSheetRowHeaderDto);
                spreadsheetRowDtoList.add(spreadsheetTempRowDto);
            });
        }
        // 删除上次存储的临时数据
        spreadsheetTempRowDao.deleteBySpreadsheetId(excelImportContext.getSpreadsheetId());
        // 保存新的
        spreadsheetTempRowDao.save(spreadsheetRowDtoList);
        excelImportContext.getExcelImportResult().setSpreadsheetId(spreadsheetDto.getId());
        return excelImportContext.getExcelImportResult();
    }

    @Override
    public void excelImportTemplateDownLoad(String termId, String templateType, OutputStream out) {
        List<RowHeaderColumn> rowHeaderColumnList = rowHeaderColumnService.getTermTemplateDataList(termId, templateType);
        Map<String, String> headerMap = new LinkedHashMap<>();
        Map<String, List<String>> dropDownsMap = new HashMap<>();
        //Map<String, ExcelCellStyle> headerStyleMap = new HashMap<>();
        rowHeaderColumnList.forEach(column -> {
                    String columnFieldMeta = column.getColumnFieldMeta();
                    headerMap.put(columnFieldMeta, column.getColumnDisplayName());
                    //TODO 模板添加下拉选，目前写死,且暂未考虑多语言
                    //证件类型
                    if (SpreadsheetRowColumnMeta.IDCARD_TYPE.equals(columnFieldMeta)) {
                        List<IdCardType> idCardTypes = Arrays.asList(IdCardType.values());
                        List<String> idCardTypeStrs = idCardTypes.stream().map(IdCardType::getName).collect(Collectors.toList());
                        dropDownsMap.put(columnFieldMeta, idCardTypeStrs);
                        //是否允许发布
                    } else if (SpreadsheetRowColumnMeta.ALLOW_PUBLISH.equals(columnFieldMeta)) {
                        List<String> dropItems = new ArrayList<>();
                        dropItems.add("是");
                        dropItems.add("否");
                        dropDownsMap.put(columnFieldMeta, dropItems);
                    }
                    /*if (BooleanUtils.toBoolean(column.getRequired())) {
                        headerStyleMap.put(columnFieldMeta, ExcelCellStyleFactory.createRequiredHeaderCellStyle());
                    }*/

                }
        );
        headerMap.put("可自定义添加", "可自定义添加");
        List<List<Object>> exampleDataList = new ArrayList<>();
        List<Object> exampleData = rowHeaderColumnService.getTermExampleDataList(termId, templateType);
        exampleData.add("样例");
        exampleDataList.add(exampleData);

        ExportParams<List<Object>> exportParams = new ExportParams<>();
        exportParams.setHeaderMap(headerMap);
        exportParams.setDropDownsMap(dropDownsMap);
        exportParams.setRowDatas(exampleDataList);
        //exportParams.setHeaderStyleMap(headerStyleMap);

        ExcelUtil.exportExcel(exportParams, out);
    }

    @Override
    public void spreadsheetDataToExcelFile(String spreadsheetId, List<String> rowIdList, OutputStream out) {
        //获取表头
        SpreadSheetRowHeaderDto spreadSheetRowHeaderDto = spreadsheetRowHeaderDao.findBySpreadsheetId(spreadsheetId);
        List<RowHeaderColumn> columnList = spreadSheetRowHeaderDto.getData();
        Map<String, String> headerMap = new LinkedHashMap<>();
        for (int i = 0; i < columnList.size(); i++) {
            RowHeaderColumn column = columnList.get(i);
            headerMap.put(column.getColumnFieldName(), column.getColumnDisplayName());
        }

        //获取数据
        List<SpreadsheetRowDto> spreadsheetRowDtoList;
        if (!CollectionUtils.isEmpty(rowIdList)) {
            spreadsheetRowDtoList = spreadsheetRowDao.findBySpreadsheetIdAndIdInOrderByCreatedAtRowIndexAsc(spreadsheetId, rowIdList);
        } else {
            spreadsheetRowDtoList = spreadsheetRowDao.findBySpreadsheetIdOrderByCreatedAtRowIndexAsc(spreadsheetId);
        }

        List<List<Object>> dataList = new ArrayList<>();
        for (SpreadsheetRowDto spreadsheetRowDto : spreadsheetRowDtoList) {
            List<Object> data = spreadsheetRowDto.getData();
            dataList.add(data);
        }

        //导出
        ExportParams<List<Object>> exportParams = new ExportParams<>();
        exportParams.setHeaderMap(headerMap);
        exportParams.setRowDatas(dataList);
        ExcelUtil.exportExcel(exportParams, out);
    }

    @Override
    public void staffRecordDataToExcelFile(SearchRequest searchRequest, List<String> idList, OutputStream out, String companyId) {
        //导出选中
        List<RowHeaderColumn> rowHeaderColumnList = rowHeaderColumnService.getStaffRecordHead();
        Map<String, String> headerMap = new LinkedHashMap<>();
        rowHeaderColumnList.forEach(column ->
                headerMap.put(column.getColumnFieldMeta(), column.getColumnDisplayName())
        );
        List<StaffRecord> staffRecordList;
        if (!CollectionUtils.isEmpty(idList)) {
            staffRecordList = staffRecordService.getList(companyId, idList);
        } else {//导出全部
            staffRecordList = staffRecordService.getAllData(companyId, searchRequest);
        }
        List<List<Object>> dataList = new ArrayList<>();
        staffRecordList.forEach(staffRecord -> {
            List<Object> list = new ArrayList<>();
            list.add(staffRecord.getStaffName());
            list.add(staffRecord.getIdCardNo());
            list.add(staffRecord.getMobileNo());
            list.add(staffRecord.getAllow() ? "启用" : "禁用");
            dataList.add(list);
        });
        Map<String, String> datePatternMap = new HashMap<>();
        datePatternMap.put(SpreadsheetRowColumnMeta.ENTRY_DATE, ExcelDateFormatUtil.PATTERN_ISO_ON_DATE);


        //导出
        ExportParams<List<Object>> exportParams = new ExportParams<>();
        exportParams.setHeaderMap(headerMap);
        exportParams.setRowDatas(dataList);
        exportParams.setDatePatternMap(datePatternMap);
        ExcelUtil.exportExcel(exportParams, out);
    }

    @Override
    public List<StaffRecord> getExcelImportStaffList(Spreadsheet spreadsheet) {
        String companyId = spreadsheet.getCompanyId();
        String spreadsheetId = spreadsheet.getId();
        List<SpreadsheetTempRowDto> spreadsheetTempRowDtoList = spreadsheetTempRowDao.findBySpreadsheetId(spreadsheetId);
        Map<String, RowHeaderColumn> rowHeaderColumnMap = rowHeaderColumnService.getSpreadsheetColumnMap(spreadsheetId);
        //1.获取公司所有员工名录信息
        List<StaffRecord> companyAllStaffRecord = staffRecordService.getCompanyAllStaffRecord(spreadsheet.getCompanyId());
        Map<String, StaffRecord> companyAllStaffRecordMap = companyAllStaffRecord.stream().collect(
                Collectors.toMap(StaffRecord::getIdCardNo, staffRecord -> staffRecord));
        List<StaffRecord> staffList = new ArrayList<>();
        int staffNameColumnIndex = rowHeaderColumnMap.get(SpreadsheetRowColumnMeta.STAFF_NAME).getColumnIndex();
        int mobileNoColumnIndex = rowHeaderColumnMap.get(SpreadsheetRowColumnMeta.MOBILE_NO).getColumnIndex();
        for (SpreadsheetTempRowDto spreadsheetTempRowDao : spreadsheetTempRowDtoList) {
            List<Object> rowData = JSONArray.parseArray(spreadsheetTempRowDao.getDataStr(), Object.class);
            String idCardNo = spreadsheetTempRowDao.getIdCardNo();
            String staffName = (String) rowData.get(staffNameColumnIndex);
            String mobileNo = (String) rowData.get(mobileNoColumnIndex);
            StaffRecord staffRecord = new StaffRecord();
            if (companyAllStaffRecordMap.containsKey(idCardNo)) {
                staffRecord = companyAllStaffRecordMap.get(idCardNo);
            } else {
                //封装需要新增的员工名录
                staffRecord.setIdCardNo(idCardNo);
                staffRecord.setCompanyId(companyId);
                staffRecord.setAllow(true);
                staffRecord.setCreatedAt(new Date());
            }
            staffRecord.setMobileNo(mobileNo);
            staffRecord.setStaffName(staffName);
            staffList.add(staffRecord);
        }
        return staffList;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Spreadsheet saveExcelData(String userId, Spreadsheet spreadsheet, List<StaffRecord> staffRecordList) {
        String spreadsheetId = spreadsheet.getId();
        SpreadsheetDto spreadsheetDto = spreadsheetDao.findOne(spreadsheetId);

        //保存员工名录
        List<StaffRecordDto> staffRecordDtoList = new ArrayList<>();
        List<UserStaffRecordDto> userSpreadsheetDtoList = new ArrayList<>();
        for (StaffRecord staffRecord : staffRecordList) {
            StaffRecordDto staffRecordDto = BeanConvertUtils.convertFor(staffRecord, new StaffRecordDto());
            staffRecordDtoList.add(staffRecordDto);
            if (staffRecordDto.getId() != null &&
                    userStaffRecordDao.countByStaffRecordIdAndUserId(staffRecord.getId(), userId) > 0) {
                continue;
            }
            UserStaffRecordDto userStaffRecordDto = new UserStaffRecordDto();
            userStaffRecordDto.setStaffRecord(staffRecordDto);
            userStaffRecordDto.setUserId(userId);
            userSpreadsheetDtoList.add(userStaffRecordDto);
        }
        staffRecordDao.save(staffRecordDtoList);
        if (!CollectionUtils.isEmpty(userSpreadsheetDtoList)) {
            userStaffRecordDao.save(userSpreadsheetDtoList);
        }

        Map<String, String> identityStaffRecordIdMap = staffRecordDtoList.stream().collect(
                Collectors.toMap(StaffRecordDto::getIdCardNo, StaffRecordDto::getId));

        List<SpreadsheetTempRowDto> spreadsheetTempRowDtoList = spreadsheetTempRowDao.findBySpreadsheetId(spreadsheetId);

        List<SpreadsheetRowDto> spreadsheetRowDtoList = new ArrayList<>();
        Map<String, RowHeaderColumn> rowHeaderColumnMap = rowHeaderColumnService.getSpreadsheetColumnMap(spreadsheetId);
        spreadsheetTempRowDtoList.forEach(spreadsheetTempRowDto -> {
            SpreadsheetRowDto spreadsheetRowDto = BeanConvertUtils.convertFor(spreadsheetTempRowDto, new SpreadsheetRowDto());
            spreadsheetRowDto.setId(null);
            if (spreadsheetRowDto.getStaffRecordId() == null) {
                spreadsheetRowDto.setStaffRecordId(identityStaffRecordIdMap.get(spreadsheetTempRowDto.getIdCardNo()));
            }
            spreadsheetRowDtoList.add(spreadsheetRowDto);
        });

        // 删除老的
        spreadsheetRowDao.deleteBySpreadsheetId(spreadsheetId);
        // 添加新的
        spreadsheetRowDao.save(spreadsheetRowDtoList);
        // 删除上次存储的临时数据
        spreadsheetTempRowDao.deleteBySpreadsheetId(spreadsheetId);
        spreadsheetDto.setStatus(1);
        spreadsheetDto.setRowCount(spreadsheetRowDtoList.size());

        spreadsheetDto.setSheetDesc(spreadsheet.getSheetDesc());
        spreadsheetDto.setUpdatedAt(new Date());
        updateSpreadsheetStatisticalItem(spreadsheetDto, spreadsheetRowDtoList, rowHeaderColumnMap);
        spreadsheetDao.save(spreadsheetDto);
        //重新计算保存合计数据
        spreadsheetService.saveFootData(spreadsheetDto.getId());
        return spreadsheet;
    }


    /**
     * 更新统计项
     *
     * @param spreadsheetDto
     * @param spreadsheetRowDtoList
     * @param rowHeaderColumnMap
     */
    private void updateSpreadsheetStatisticalItem(SpreadsheetDto spreadsheetDto,
                                                  List<SpreadsheetRowDto> spreadsheetRowDtoList, Map<String, RowHeaderColumn> rowHeaderColumnMap) {
        // 总数
        spreadsheetDto.setRowCount(spreadsheetRowDtoList.size());

        int publishedCount = 0;
        int notAllowPublishCount = 0;
        int allowPublishColumnIndex = rowHeaderColumnMap.get(SpreadsheetRowColumnMeta.ALLOW_PUBLISH).getColumnIndex();
        for (SpreadsheetRowDto spreadsheetRow : spreadsheetRowDtoList) {
            //List<Object> rowValueList = JSONArray.parseArray(spreadsheetRow.getDataStr(), Object.class);
            List<Object> rowValueList = spreadsheetRow.getData();
            if (spreadsheetRow.getPublishStatus()) {
                publishedCount++;
            }
            Boolean allowPublish = (Boolean) rowValueList.get(allowPublishColumnIndex);
            if (allowPublish == null || !allowPublish) {
                notAllowPublishCount++;
            }
        }

        // 已发布
        spreadsheetDto.setPublishedCount(publishedCount);

        // 未发布
        spreadsheetDto.setUnpublishedCount(spreadsheetDto.getRowCount() - publishedCount - notAllowPublishCount);

        //不允许发布
        spreadsheetDto.setNotAllowPublishCount(notAllowPublishCount);
    }

    @Override
    public Spreadsheet cancelSaveExcelData(String spreadsheetId) {
        SpreadsheetDto spreadsheetDto = spreadsheetDao.findOne(spreadsheetId);
        if (spreadsheetDto != null) {
            spreadsheetTempRowDao.deleteBySpreadsheetId(spreadsheetId);
            if (spreadsheetDto.getStatus() == 0) {
                spreadsheetColumnDao.deleteBySpreadsheetId(spreadsheetId);
                spreadsheetDao.delete(spreadsheetDto.getId());
                spreadsheetRowHeaderDao.deleteBySpreadsheetId(spreadsheetId);
                return null;
            }
        }
        return BeanConvertUtils.convertFor(spreadsheetDto, new Spreadsheet());
    }

    @Override
    public Spreadsheet deleteTempExcelRow(String spreadsheetId, List<String> idList) {
        spreadsheetTempRowDao.deleteByIdIn(idList);
        /*long count = spreadsheetTempRowDao.countBySpreadsheetId(spreadsheetId);
        if(count == 0) {
            return cancelSaveExcelData(spreadsheetId);
        }*/
        SpreadsheetDto spreadsheetDto = spreadsheetDao.findOne(spreadsheetId);
        return BeanConvertUtils.convertFor(spreadsheetDto, new Spreadsheet());
    }

    @Override
    public Spreadsheet deleteTempExcelRowAll(String spreadsheetId) {
        spreadsheetTempRowDao.deleteBySpreadsheetId(spreadsheetId);
        SpreadsheetDto spreadsheetDto = spreadsheetDao.findOne(spreadsheetId);
        return BeanConvertUtils.convertFor(spreadsheetDto, new Spreadsheet());
        //return cancelSaveExcelData(spreadsheetId);
    }

    private <T> void setEntityFieldValue(Class<T> clazz, SpreadsheetRow row, String fieldName, Object fieldValue) {
        // TODO 此处可优化，固定字段可以先拿出来
        try {
            Field field = clazz.getDeclaredField(fieldName);
            field.setAccessible(true);
            field.set(row, fieldValue);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private Object getEntityFieldValue(SpreadsheetRow row, String fieldName) {
        try {
            Field field = SpreadsheetRow.class.getDeclaredField(fieldName);
            field.setAccessible(true);
            return field.get(row);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取身份证类型的字面量
     * TODO 暂不支持证件类型英文
     *
     * @param name
     * @return
     */
    private String getIdCardTypeName(String name, Map<String, IdCardType> idCardTypeMap) {
        if (!idCardTypeMap.containsKey(name)) {
            return null;
        }
        return idCardTypeMap.get(name).name();
    }
}
